package com.teamone.etl.datasource.service.impl;

import cn.hutool.core.date.DateUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.alibaba.druid.DbType;
import com.alibaba.druid.sql.parser.SQLParserUtils;
import com.alibaba.druid.sql.parser.SQLStatementParser;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.teamone.etl.datasource.dao.BiAnalysisDao;
import com.teamone.etl.datasource.dao.DataSourceDao;
import com.teamone.etl.datasource.entity.BiAnalysis;
import com.teamone.etl.datasource.entity.DataSource;
import com.teamone.etl.datasource.pool.JdbcConnector;
import com.teamone.etl.datasource.pool.RedisConnector;
import com.teamone.etl.datasource.service.DataSourceService;
import com.teamone.etl.exception.CommonCode;
import com.teamone.etl.exception.CustomException;
import com.teamone.etl.util.DesensitizationUtilPlus;
import com.teamone.etl.util.DesensitizationUtilPro;
import com.teamone.etl.util.NameUtil;
import java.sql.*;
import java.util.Date;
import java.util.*;
import java.util.concurrent.*;
import javax.annotation.Resource;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;

import static com.teamone.etl.constant.EtlConstant.MySqlConstant.*;
import static com.teamone.etl.constant.EtlConstant.TypeConstant.*;

/**
 (DataSource)表服务实现类

 @author makejava
 @since 2021-01-06 14:22:51 */
@Service("dataSourceService")
@Transactional(rollbackFor = Exception.class)
public class DataSourceServiceImpl extends ServiceImpl<DataSourceDao, DataSource> implements DataSourceService {

    @Resource
    private BiAnalysisDao biAnalysisDao;

    @Override
    public Boolean removeByIdsAndCheckIsConnect (List<Integer> idList) throws CustomException {
        ForkJoinPool forkJoinPool = new ForkJoinPool();
        //并行执行删除任务
        ForkJoinTaskDelete task = new ForkJoinTaskDelete(1, idList.size(), idList.stream().mapToInt(id -> id).toArray());
        //向线程池提交任务
        ForkJoinTask<Void> result = forkJoinPool.submit(task);
        try {
            result.get();
        } catch (InterruptedException | ExecutionException e) {
            throw new CustomException(CommonCode.SERVER_ERROR);

        }
        return true;
    }

    /**
     @param dataSource 要修改的数据源
     */
    @Override
    @Transactional(rollbackFor = Exception.class)
    public void updateSource (DataSource dataSource) {
        baseMapper.updateSource(dataSource);
    }

    @Override
    public PageInfo<DataSource> search (Integer typeId, String name, Integer page, Integer size) {
        size = (size > 50) ? 50 : size;
        PageHelper.startPage(page, size);
        List<DataSource> search = baseMapper.seach(typeId, name);
        return new PageInfo<>(search);
    }

    @Override
    public List<DataSource> getAllNotDisabled () {
        return baseMapper.getAllNotDisabled();
    }

    @Override
    public DataSource getDataSourceById (Integer id) {
        return baseMapper.getDataSourceById(id);
    }

    @Override
    public Map<String, Object> dataAnalysis (Integer dataSourceId, String dataBaseName, String tableName, String sql, boolean shouldExecuteRuleEngine) throws CustomException {
        /*
         * 校验SQL语句
         */
        String sqlType = sql.split(SPACE)[0].toUpperCase();
        if(StringUtils.isEmpty(sql)){
            throw new CustomException(CommonCode.SQL_PARAM_ERROR);
        }else if(CREATE.equals(sqlType) || ALTER.equals(sqlType) || DROP.equals(sqlType)){
            throw new CustomException(CommonCode.SQL_UNSUPPORTED);
        }
        SQLStatementParser parser = SQLParserUtils.createSQLStatementParser(sql, DbType.mysql);
        try { parser.parseStatementList(); }catch (Exception e){ throw new CustomException(CommonCode.SQL_PARAM_ERROR); }
        //获取数据源链接 为空抛出异常
        Connection connection = JdbcConnector.JDBC_POOL_MAP.get(dataSourceId);
        if(connection==null){ throw new CustomException(CommonCode.CONNECTION_FAIL); }
        //初始化变量
        Map<String, Object> response = new ConcurrentHashMap<>(16);
        String filePath = getFilePath();
        try (ExcelWriter writer = new ExcelWriter(filePath); Statement stmt = connection.createStatement()) {
            List<Map<String, String>> results = new ArrayList<>(16);
            BiAnalysis biAnalysis = new BiAnalysis(sql, dataSourceId, dataBaseName, tableName, filePath);
            //声明连接正在工作
            connection.setClientInfo("isworking", "working");
            stmt.execute("use " + dataBaseName);
            //判断SQL语句类型
            if (SELECT.equalsIgnoreCase(sql.split(SPACE)[0])) {
                writer.merge(10, sql);
                try(ResultSet rs = stmt.executeQuery(sql)){
                    //获取表结构
                    ResultSetMetaData metaData = rs.getMetaData();
                    while (rs.next()) {
                        //每次循环 都声明连接正在工作,防止其他操作将信息修改
                        connection.setClientInfo("isworking", "working");
                        //列 数量
                        int columnCount = metaData.getColumnCount();
                        //每一行结果集 的集合
                        Map<String, String> row = new HashMap<>(columnCount);
                        //字段 类型+长度+精度 集合
                        Map<String, String> columnTypeNameAndDisplaySizeAndScale = new HashMap<>(columnCount);
                        List<String> columnList = new ArrayList<>();
                        //循环取值
                        for (int i = 1; i <= columnCount; i++) {
                            //获得列别名(如果设定) 否则得到列名
                            String columnLabel = metaData.getColumnLabel(i);
                            StringBuilder sb = new StringBuilder(columnLabel);
                            //对重复列名处理
                            while (row.containsKey(columnLabel)) {
                                sb.append("_");
                                columnLabel = sb.toString();
                            }
                            /*
                             * while (rs.next())循环第一遍获得字段信息
                             * 类型+长度+精度
                             */
                            if (response.size() == 0) {
                                columnTypeNameAndDisplaySizeAndScale.put(columnLabel, getColumnInfo(columnLabel, metaData, i));
                                columnList.add(columnLabel);
                            }
                            //值
                            String value = rs.getString(i);
                            /*
                             * 将列名转大写,判断是否是关键字
                             * 如果不是关键字则不进行脱敏操作
                             */
                            if (KEY_WORD.contains(columnLabel.toUpperCase()) && shouldExecuteRuleEngine) {
                                switch (columnLabel.toUpperCase()){
                                    case PHONE:case TEL:
                                        value = DesensitizationUtilPlus.mobileEncrypt(value); break;
                                    case MAIL:case EMAIL:
                                        value = DesensitizationUtilPlus.emailEncrypt(value); break;
                                    case CARD:
                                        value = DesensitizationUtilPro.idcard(value); break;
                                    case NAME:case USERNAME:case PASSWORD:case PWD:case ADDR:case ADDRESS:
                                        value = DesensitizationUtilPro.process(value, 2, 2,"****"); break;
                                    default:break;
                                }
                            }
                            row.put(columnLabel, value);
                        }
                        //第一遍循环,存入表结构
                        if (response.size() == 0) {
                            response.put("columns", columnList);
                            response.put("structure", columnTypeNameAndDisplaySizeAndScale);
                            writer.write(Collections.singleton(columnTypeNameAndDisplaySizeAndScale));
                        }
                        results.add(row);
                        //向Excel写入一行数据
                        writer.write(Collections.singleton(row));
                    }
                }
                response.put("results", results);
                response.put("fileUrl", filePath);
            } else {
                throw new CustomException(CommonCode.SQL_PARAM_ERROR);
            }
            biAnalysisDao.save(biAnalysis);
        } catch (Exception e) {
            e.printStackTrace();
            throw new CustomException(CommonCode.CONNECTION_FAIL);
        } finally {
            try {
                connection.setClientInfo("isworking", "0");
            } catch (SQLClientInfoException ignored) {}
        }
        return response;
    }

    @Override
    public List<DataSource> getAllNotDisabledAndClearSensitiveInformation () {
        return baseMapper.getAllNotDisabledAndClearSensitiveInformation();
    }

    @Override
    public List<DataSource> getAllNotDisabledByDescriptionAndClearSensitiveInformation (String description) {
        return baseMapper.getAllNotDisabledByDescriptionAndClearSensitiveInformation(description);
    }

    @Override
    public boolean testMysqlConnect (DataSource dataSource) {
        return JdbcConnector.isConnected(dataSource);
    }

    @Override
    public boolean testRedisConnect (DataSource dataSource) {
        return RedisConnector.isConnected(dataSource);
    }

    @Override
    public List<DataSource> selectAll () {
        return baseMapper.selectListSql();
    }

    class ForkJoinTaskDelete extends RecursiveAction {

        private static final int THRESHOLD = 2;
        private final int start;
        private final int end;
        private final int[] idList;

        public ForkJoinTaskDelete (int start, int end, int[] idList) {
            this.start = start;
            this.end = end;
            this.idList = idList;
        }

        @Override
        protected void compute () {
            //任务足够小才执行
            boolean canComput = (end - start) <= THRESHOLD;
            if (canComput) {
                for (int i = 0; i < idList.length; i++) {
                    for (Integer id : idList) {
                        if (id != 0) {
                            Connection connection = JdbcConnector.JDBC_POOL_MAP.get(id);
                            if (connection != null) {
                                boolean notWorked = JdbcConnector.isNotWorked(connection);
                                while (! notWorked) {
                                    notWorked = JdbcConnector.isNotWorked(connection);
                                }
                                try {
                                    connection.close();
                                } catch (SQLException throwables) {
                                    throwables.printStackTrace();
                                }
                                JdbcConnector.JDBC_POOL_MAP.remove(id);
                            } else {
                                boolean connected = RedisConnector.isConnected(getDataSourceById(id));
                                if (connected) {
                                    RedisConnector.close(id);
                                }
                                RedisConnector.redisPoolMap.remove(id);
                            }
                            baseMapper.deleteByIdSql(id);
                        }
                    }
                }
            } else {
                int middle = (start + end) / 2;
                int[] leftIdArray = new int[middle];
                if (middle > 0) {
                    for (int i = 0; i < middle; i++) {
                        if (idList[i] != 0) {
                            leftIdArray[i] = idList[i];
                        }
                    }
                }
                int[] rightIdArray = new int[end - middle];
                if (end - (middle - 1) >= 0) {
                    int j = middle;
                    for (int i = 0; i < rightIdArray.length; i++) {
//                        if (idList[j] != 0 && middle != idList.length) {
                        if (idList[j] != 0) {
                            rightIdArray[i] = idList[j];
                            j++;
                        }
                    }
                }

                ForkJoinTaskDelete leftTask = new ForkJoinTaskDelete(start, middle, leftIdArray);
                ForkJoinTaskDelete rightTask = new ForkJoinTaskDelete(middle, end, rightIdArray);

                leftTask.fork();
                rightTask.fork();

                leftTask.join();
                rightTask.join();
            }
        }
    }

    private String getFilePath(){
        Date date = DateUtil.date();
        return System.getProperty("user.dir") +
                "\\GeneratedExcel\\" +
                DateUtil.year(date) +
                "\\" +
                (DateUtil.month(date) + 1) +
                "\\" +
                DateUtil.dayOfMonth(date) +
                "\\" +
                DateUtil.hour(date, true) +
                "_" +
                DateUtil.minute(date) +
                "_" +
                DateUtil.second(date) +
                "_" +
                NameUtil.getRandom(10, NameUtil.TYPE.LETTER_CAPITAL_NUMBER) +
                ".xlsx";
    }

    private String getColumnInfo(String columnLabel,ResultSetMetaData metaData,int i) throws CustomException {
        String info;
        try {
            info = columnLabel +
                    "{" +
                    metaData.getColumnTypeName(i) +
                    "(" +
                    metaData.getColumnDisplaySize(i) +
                    "." +
                    metaData.getScale(i) +
                    ")}";
        } catch (SQLException throwables) {
            throw new CustomException(CommonCode.CONNECTION_FAIL);
        }
        return info;
    }

}